package com.ztz.services;

import com.ztz.dao.RepetitionDataDao;
import com.ztz.execl.ReadExcel;
import com.ztz.execl.ReadExcelTest;
import hibernatebeans.entity.GeneralLedgerEntity;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import pers.lt.jdbc.Page;
import pers.lt.tools.StringFacs;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.*;

@Component("RepetitionDataServices")
@Transactional
public class RepetitionDataServices {
    @Autowired
    private RepetitionDataDao repetitionDataDao;

    public Page repetitionDataList(Page page) {
        return repetitionDataDao.repetitionDataList(page);
    }
    //条件查询
    public Page querylist(Page page ) {
        return repetitionDataDao.querylist(page);
    }
    //不重复数据查询
    public Page querys(Page page,GeneralLedgerEntity generalLedgerEntity ) {
        return repetitionDataDao.querys(page,generalLedgerEntity);
    }
     public String readExcelFile(MultipartFile file) {
             String result = "";
             //创建处理EXCEL的类
             ReadExcel readExcel = new ReadExcel();
             //解析excel，获取上传的事件单
           List<Map<String, Object>> userList = readExcel.getExcelInfo(file);
           for(Map<String, Object> user:userList){
               common.ArrayList arrayList=new common.ArrayList();
               String[] BZ=arrayList.array();
               String line_No=user.get(BZ[0]).toString();
               String weld_No=user.get(BZ[2]).toString();
               String prpesize=user.get(BZ[6]).toString();
               String cut=user.get(BZ[12]).toString();
               String fab=user.get(BZ[13]).toString();
               String reason=user.get(BZ[14]).toString();
               String check1=user.get(BZ[15]).toString();
               String check2=user.get(BZ[16]).toString();
               String check3=user.get(BZ[17]).toString();
               String check4=user.get(BZ[18]).toString();
               String a="";
               String arr[]=new String[14];
               String arr1[]=new String[14];
              if(!check1.equals("")){
                  a=check1;
              }else if(!check2.equals("")){
                  a=check2;
              }else if(!check3.equals("")){
                  a=check3;
              }else if(!check4.equals("")){
                  a=check4;
              }

               if(a.equals("RT")){
                   arr= new String[]{ "RT检测比例", "RT委托来源", "RT委托单号", "RT委托日期", "RT报告号", "RT报告上检测日期", "RT现场实际检测日期"
                           , "RT出报告日期", "RT缺陷", "RT评定结果", "RT每天报告焊口数", "RT焊口点数", "RT报告递交情况", "RT备注"};
               }else if(a.equals("MT")||a.equals("PT")){
                   arr= new String[]{"MTPT检测比例", "MTPT委托来源", "MTPT委托单号", "MTPT委托日期", "MTPT报告号", "MTPT报告上检测日期", "MTPT现场实际检测日期", "MTPT出报告日期", "MTPT缺陷", "MTPT评定结果"
                           , "MTPT每天报告焊口数", "MTPT焊口点数", "MTPT报告递交情况", "MTPT备注"};
               }else if(a.equals("HT")){

                   arr= new String[]{ "HT检测比例", "HT委托来源", "HT委托单号", "HT委托日期", "HT报告号", "HT报告上检测日期"
                           , "HT现场实际检测日期", "HT出报告日期", "HT缺陷", "HT评定结果", "HT每天报告焊口数", "HT焊口点数", "HT报告递交情况", "HT备注"};
               }else if(a.equals("PMI")){
                   arr= new String[]{ "PMI检测比例", "PMI委托来源", "PMI委托单号"
                           , "PMI委托日期", "PMI报告号", "PMI报告上检测日期", "PMI现场实际检测日期", "PMI出报告日期"
                           , "PMI缺陷", "PMI评定结果", "PMI每天报告焊口数", "PMI焊口点数", "PMI报告递交情况", "PMI备注"};
               }
               for(int n=0;n<arr.length;n++){
                  String va=user.get(arr[n]).toString();
                  arr1[n]=va;
               }

               //判断重复不重复数据
               String zh=line_No+weld_No+prpesize+cut+fab+reason;
               //查询检测方式
               List sercListjcfs=repetitionDataDao.sercaListjcsf(zh);

               Map map=new HashMap();
               Map mapjcfs=new HashMap();
               StringBuffer str=new StringBuffer();
                 for(int i=0;i<BZ.length;i++){
                       if(i<=BZ.length-1){
                           str.append(",").append("'").append(user.get(BZ[i])).append("'");
                       }else {
                           str.append(user.get(BZ[i]));
                       }
                   }

                   String req="";
                   String rup="";
                   if(sercListjcfs.size()>0 ) {
                       for (int j = 0; j < sercListjcfs.size(); j++) {
                           map = (Map) sercListjcfs.get(j);
                           String zf = map.get("zf").toString();
                           String id = map.get("id").toString();
                           String checkjc1 = map.get("check1").toString();
                           String checkjc2 = map.get("check2").toString();
                           String checkjc3 = map.get("check3").toString();
                           String checkjc4 = map.get("check4").toString();
                           if (zf.equals(zh)) { //前16位数一样
                                   if (!check1.equals("")) {//说明导入的检测方式为check1
                                       if (!checkjc1.equals("")) {//判断数据库中的的检测方式1是否为空,不空则为重复数据，插入到重复表
                                           //插入重复表
                                           repetitionDataDao.insertUserRepetition(str);
                                       } else {
                                           //update
                                           repetitionDataDao.updateUserRepetition(id, check1, "check1",arr1,arr);
                                       }
                                   } else if (!check2.equals("")) {
                                       if (!checkjc2.equals("")) {//判断数据库中的的检测方式1是否为空,不空则为重复数据，插入到重复表
                                           //插入重复表
                                           repetitionDataDao.insertUserRepetition(str);
                                       } else {
                                           //update
                                           repetitionDataDao.updateUserRepetition(id, check2, "check2",arr1,arr);
                                       }
                                   } else if (!check3.equals("")) {
                                       if (!checkjc3.equals("")) {//判断数据库中的的检测方式1是否为空,不空则为重复数据，插入到重复表
                                           //插入重复表
                                           repetitionDataDao.insertUserRepetition(str);
                                       } else {
                                           //update
                                           repetitionDataDao.updateUserRepetition(id, check3, "check3",arr1,arr);
                                       }
                                   } else if (!check4.equals("")) {
                                       if (!checkjc4.equals("")) {//判断数据库中的的检测方式1是否为空,不空则为重复数据，插入到重复表
                                           //插入重复表
                                           repetitionDataDao.insertUserRepetition(str);
                                       } else {
                                           //update
                                           repetitionDataDao.updateUserRepetition(id, check4, "check4",arr1,arr);
                                       }
                                   }

                           } else {//前16位不一样即为新数据
                               repetitionDataDao.inserytUser(str);
                           }

                       }
                   }else {
                       repetitionDataDao.inserytUser(str);
                   }
                }
            if(userList != null && !userList.isEmpty()){
                   result = "上传成功";
                 }else{
                     result = "上传失败";
                 }
             return result;
        }

    /**
     *
     * @param request
     * @param line_No
     *@param weldNo @return
     * 数据查询按钮导出
     */
    public String exportExcel(HttpServletRequest request, String line_No, String weldNo,String flag) throws IOException {
        String fileName="mtpt.xlsx";
        //输入模板文件
        String filePath = StringFacs.getClassRoot(request) + "mtpt.xlsx";//文件路径
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePath));
        SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 1000);
        //从第几页开始
        Sheet sheet = workbook.getSheet("sheet" + (1));
        List<Map> paList = repetitionDataDao.exportExcel1(line_No,weldNo,flag);
        common.ArrayList arrayList=new common.ArrayList();
        String[] BZ=arrayList.array();
        for (int i = 0; i < paList.size(); i++) {
            Row row = sheet.createRow(i+1);
            Map m = paList.get(i);
            for (int j = 0; j < BZ.length; j++) {
                //System.out.println(m.get(BZ[j]).toString());
                Object a=m.get(BZ[j]);
                if(a==null || a.equals("null")){
                    a="";
                }
               // System.out.println(a);
                row.createCell(j).setCellValue(a.toString());
               //System.out.println(a);
               // row.createCell(j).setCellValue(m.get(BZ[j]).toString());
               //row.createCell(j).setCellValue((m.get(BZ[j]).equals("null")) ? "" : m.get(BZ[j]).toString());
            }
        }
        fileName = new Date().getTime() + ".xlsx";
        String filePathName = StringFacs.getWebRoot(request) + "\\upload\\" + fileName;
        FileOutputStream out = new FileOutputStream(filePathName);
        workbook.write(out);
        out.close();
        return fileName;
    }

    @Transactional(readOnly = false)
    public void sjDelete(String ids) throws SQLException {
        String[] lar_ids = ids.split("~t");
        List<GeneralLedgerEntity> list = new ArrayList<GeneralLedgerEntity>();
        GeneralLedgerEntity generalLedgerEntity = null;
        //组装id的集合
        for (String string : lar_ids) {
            generalLedgerEntity = new GeneralLedgerEntity();
            generalLedgerEntity.setId(string);
            list.add(generalLedgerEntity);
        }
        String returnStr = "删除成功!";
        try {
            repetitionDataDao.sjDelete(list);
        } catch (SQLException e) {
            e.printStackTrace();
            returnStr = "删除失败!";
        }
    }

    public void deleteAll(HttpServletRequest request) {
        repetitionDataDao.deleteAll();
    }


    /**
     * ----------------------------------------------------------------------------------------------------
     * @return
     */
    //导入查询 保存临时表
    public String readExcelFilelist(MultipartFile file) {
        String result = "";
        //创建处理EXCEL的类
        ReadExcelTest readExcel = new ReadExcelTest();
        //解析excel，获取上传的事件单
        List<Map<String, Object>> userList = readExcel.getExcelInfo(file);
        //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
        common.ArrayList array=new common.ArrayList();
        String[] BZ=array.arrayzb();
        // String[] BZ = {"line_No ", "sex", "repse", "arr"};
        for(Map<String, Object> user:userList){
            StringBuffer str=new StringBuffer();
            for(int i=0;i<BZ.length;i++){
                if(i<=BZ.length-1){
                    str.append(",").append("'").append(user.get(BZ[i])).append("'");
                }else {
                    str.append(user.get(BZ[i]));
                }
            }
            boolean ret = repetitionDataDao.insertUser(str);
            if(ret == false){
                result = "插入数据库失败";
            }
        }
        if(userList != null && !userList.isEmpty()){
            result = "上传成功";
        }else{
            result = "上传失败";
        }
        return result;
    }


    /**
     *
     * 导出根据导入数据导出
     */
    public String exportExcellist(HttpServletRequest request) throws IOException, SQLException {
        String fileName="mtptdrcx.xlsx";
        //输入模板文件
        String filePath = StringFacs.getClassRoot(request) + "mtptdrcx.xlsx";//文件路径
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePath));
        SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 1000);
        //从第几页开始
        Sheet sheet = workbook.getSheet("sheet" + (1));
        common.ArrayList arrayList=new common.ArrayList();
        String[] arr=arrayList.arraydc();
        List<Map> paList = repetitionDataDao.exportExcellist1();
        String str = repetitionDataDao.exportExcellist();
        String[] strs=str.split("-");
        Map<Integer, Object>  firstTitles = new HashMap();
        //第一行前19列
        for(int j=0;j<arr.length;++j){
            firstTitles.put(j,arr[j].toString());
        }
        //第一行19列后
        for(int i=0;i<strs.length;i++){
            if(!strs[i].toString().equals("")) {
                firstTitles.put(18 + i,strs[i].toString());
            }
        }
        genSheetHead(sheet, 0, firstTitles);
        //前19列数据
        for (int i = 0; i < paList.size(); i++) {
            Row rowh = sheet.createRow(i+1);
            Map m = paList.get(i);
            for (int k = 0; k < arr.length; k++) {
                Object a=m.get(arr[k]);
                if(a==null || a.equals("null")){
                    a="";
                }
                rowh.createCell(k).setCellValue(a.toString());
            }
            //19列后数据
            for (int f = 0; f < strs.length; f++) {
                if (!strs[f].toString().equals("")) {
                    Object a=m.get(strs[f]);
                    if(a==null || a.equals("null")){
                        a="";
                    }
                    rowh.createCell(18 + f).setCellValue(a.toString());
                   // rowh.createCell(18 + f).setCellValue(m.get(strs[f]).equals("null") ? " " : m.get(strs[f]).toString());
                }

            }
        }
        fileName = new Date().getTime() + ".xlsx";
        String filePathName = StringFacs.getWebRoot(request) + "\\upload\\" + fileName;
        FileOutputStream out = new FileOutputStream(filePathName);
        workbook.write(out);
        out.close();
        return fileName;


      /*
        String fileName="";
        try {
            String filePath = StringFacs.getClassRoot(request) + "mtpt.xlsx";//文件路径
            FileInputStream in = new FileInputStream(filePath);
            String str = repetitionDataDao.exportExcellist();
            String[] strs=str.split("-");
            XSSFWorkbook workbook = new XSSFWorkbook(in);//创建Excel文件(Workbook)
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow xssfRow = sheet.createRow(0);
            common.ArrayList arrayList=new common.ArrayList();
            String[] arr=arrayList.arraydc();
            List<Map> paList = repetitionDataDao.exportExcellist1();
            //前19列
            for(int j=0;j<arr.length;++j){
                xssfRow.createCell(0+j).setCellValue(arr[j].toString());
            }
            //19列后
            for(int i=0;i<strs.length;i++){
                if(!strs[i].toString().equals("")) {
                    System.out.println(strs[i].toString());
                    xssfRow.createCell(18 + i).setCellValue(strs[i].toString());
                }
            }
            //前19列数据
            for (int i = 0; i < paList.size(); i++) {
                xssfRow = sheet.createRow(i + 1);
                Map m = paList.get(i);
                for (int k = 0; k < arr.length; k++) {
                    System.out.println(m.get(arr[k]));
                    xssfRow.createCell(k).setCellValue(StringFacs.isEmpty(m.get(arr[k])) ? " " : m.get(arr[k]).toString());
                }
                System.out.println("strs"+strs);
                //前19列后数据
                for (int f = 0; f < strs.length; f++) {
                    if(!strs[f].toString().equals("")){
                        xssfRow.createCell(18+f).setCellValue(m.get(strs[f]).equals("null") ? " " : m.get(strs[f]).toString());
                    }

                }
            }
            fileName = new Date().getTime() + ".xlsx";
            String filePathName = StringFacs.getWebRoot(request) + "\\upload\\" + fileName;
            FileOutputStream out = new FileOutputStream(filePathName);
            workbook.write(out);//保存Excel文件
            out.close();//关闭文件流

        }catch (Exception e){
            fileName="mtpt.xlsx";
            e.printStackTrace();
        }
        return fileName;*/
    }

//删除临时表
    public void del() {
        repetitionDataDao.del();
    }
    /**
     * 写入标题
     *
     * @param sheet
     * @param rowNum 第几行的行号
     * @param values key:第几列的列号  value:值
     */
    public static void genSheetHead(Sheet sheet, int rowNum, Map<Integer, Object> values) {

        Row row = sheet.createRow(rowNum);
        for (Integer cellNum : values.keySet()) {
            Cell cell = row.createCell(cellNum);
            Object value = values.get(cellNum);
            generateValue(value, cell);
        }
    }

    /**
     * @param row
     * @param cellNum 第几列的列号
     * @param value   值
     */
    public static void createCell(Row row, int cellNum, Object value) {
        Cell cell = row.createCell(cellNum);
        generateValue(value, cell);
    }

    private static void generateValue(Object value, Cell cell) {
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
        } else if (value instanceof RichTextString) {
            cell.setCellValue((RichTextString) value);
        }
    }
}
